package tech.wetech.admin.web.controller.system;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import tech.wetech.admin.annotation.SystemLog;
import tech.wetech.admin.common.base.Page;
import tech.wetech.admin.common.base.PageResultSet;
import tech.wetech.admin.common.base.Result;
import tech.wetech.admin.common.utils.ExcelUtil;
import tech.wetech.admin.model.system.Bill;
import tech.wetech.admin.model.system.BillExample;
import tech.wetech.admin.model.system.BizException;
import tech.wetech.admin.service.system.BillService;
import tech.wetech.admin.service.system.OrganizationService;
import tech.wetech.admin.service.system.RoleService;
import tech.wetech.admin.web.controller.base.BaseController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.jsp.tagext.PageData;
import javax.validation.Valid;
import java.io.*;
import java.math.BigDecimal;
import java.sql.Date;
import java.text.NumberFormat;
import java.time.DateTimeException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Controller
@RequestMapping("/bill")
public class BillController extends BaseController {

    @Autowired
    private BillService billService;

    @Autowired
    private OrganizationService organizationService;

    @Autowired
    private RoleService roleService;

    @RequestMapping(method = RequestMethod.GET)
    @RequiresPermissions("bill:view")
    public String toPage(Model model) {
        setCommonData(model);
        return "system/bill";
    }

    @ResponseBody
    @RequestMapping("/list")
    @RequiresPermissions("bill:view")
    public PageResultSet<Bill> list(Page page) {
        return billService.findByPage(page);
    }

    @ResponseBody
    @RequestMapping(value = "/create", method = RequestMethod.POST)
    @RequiresPermissions("bill:create")
    @SystemLog("用户管理创建用户")
    public Result create(@Valid Bill bill) {
        billService.createBill(bill);
        return Result.Success();
    }

    @ResponseBody
    @RequestMapping(value = "/update", method = RequestMethod.POST)
    @RequiresPermissions("bill:update")
    @SystemLog("用户管理更新用户")
    public Result update(Bill bill) {
        billService.updateBill(bill);
        return Result.Success();
    }

    @ResponseBody
    @RequestMapping(value = "/delete", method = RequestMethod.POST)
    @RequiresPermissions("bill:delete")
    @SystemLog("用户管理删除用户")
    public Result delete(@RequestParam("id") Long[] ids, HttpServletRequest request) {
        Arrays.asList(ids).forEach(id -> billService.deleteBill(id));
        return Result.Success();
    }


    private void setCommonData(Model model) {
        model.addAttribute("organizationList", organizationService.findAll());
        model.addAttribute("roleList", roleService.findAll());
    }

    @RequestMapping(value = "/exportTemplate", method = RequestMethod.GET)
    public void exportTemplate(HttpServletResponse response) {
        try {
            // 创建Excel表格工作簿
            XSSFWorkbook wb = new XSSFWorkbook();
            //sheet命名
            Sheet sheet = wb.createSheet( "账单信息导入模板" );
            //样式设置
            XSSFCellStyle style = wb.createCellStyle();
            //设置填充方案
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());

            Row row = sheet.createRow(0);
            ArrayList<String> cols = new ArrayList<String>();
            cols.add( "金额" );
            cols.add( "收支类型");
            cols.add("付款事由" );
            cols.add( "付款方式" );
            cols.add( "姓名" );
            cols.add( "关系" );
            cols.add( "收支日期" );
            for (int i = 0, j = cols.size(); i < j; i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(style);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(cols.get(i));
//                // 设置宽度,自适应 - 开始
                int columnWidth = sheet.getColumnWidth(i) / 256;
                int length = cols.get(i).getBytes("UTF-8").length + 1;
                if (columnWidth < length + 1) {
                    columnWidth = length + 1;
                }
                sheet.setColumnWidth(i, columnWidth * 256);
                // 设置宽度,自适应 - 结束
            }
            //sheet命名
            XSSFSheet tplDesSheet = wb.createSheet( "模板说明" );
            // 设置宽度,自适应 - 开始
            tplDesSheet.setColumnWidth(0, 25000);
            String[] info = new String[]{ "模板的一些说明","注：1、若学号为数字，请设置单元格为文本类型，避免数字省略出错\n2、保持对应关系，避免数据异常" };
            for (int i = 0; i < info.length; i++) {
                XSSFRow tplrow = tplDesSheet.createRow(i);
                Cell tplcell = tplrow.createCell(0);
                // tplcell.setWrap(true);
                if (i == (info.length - 1)) {
                    tplcell.setCellStyle(style);
                }
                tplcell.setCellType(CellType.STRING);
                tplcell.setCellValue(info[i]);
            }
            response.reset();
            //导出表名称，但Postman统一为response,网页上下载会以此命名
            String fileName = "账单信息导入模板" ;
            //设置类型为.xlsx
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            //导出表名称，但Postman统一为response,网页上下载会以此命名
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes(), "iso-8859-1") + ".xlsx");
            wb.write(response.getOutputStream());
        } catch (Exception e) {
            System.out.println("导出模板失败" + e.getMessage());
        }
    }


    /**
     * 导出报表
     *
     * @return
     */
    @RequestMapping(value = "/exportExcel",method = RequestMethod.POST)
    @ResponseBody
    public void exportExcel(HttpServletResponse response) throws Exception {
        //获取数据
       List<Bill> billList = new ArrayList<Bill>();
       BillExample billExample = new BillExample();
       billList = billService.findByExample(billExample);

        //excel标题
        String[] title = {"ID", "金额", "收支类型", "收支事由","收支方式","姓名","关系","收支日期"};

        //excel文件名
        String fileName = "账单信息表" + System.currentTimeMillis() + ".xls";

        //sheet名
        String sheetName = "账单信息表";

        String [][] content = new String[billList.size()][7];

        for (int i = 0; i < billList.size(); i++) {
            content[i] = new String[title.length];
            Bill obj = billList.get(i);
            content[i][0] = obj.getId().toString();
            content[i][1] = obj.getCash().toString();
            content[i][2] = obj.getPtype();
            content[i][3] = obj.getReason();
            content[i][4] = obj.getPayment();
            content[i][5] = obj.getName();
            content[i][6] = obj.getRelationship();
            content[i][7] = obj.getPdate().toString();
        }

        //创建HSSFWorkbook
        HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);

        //响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 发送响应流方法
     */
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    @RequestMapping(value = "/importExcel",method = RequestMethod.POST)
    @ResponseBody
    public Result importExcel(MultipartFile file) throws Exception {
        //判断excel格式
        Workbook wb = null;
        String fileName = file.getOriginalFilename();
        String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
        InputStream ins = file.getInputStream();
        if (fileType.equals("xls")) {
            wb = new HSSFWorkbook(ins);
        } else if (fileType.equals("xlsx")) {
            wb = new XSSFWorkbook(ins);
        } else {
            return Result.Failure("1","不是excel文件","不是excel文件");
        }

        //解析Excel文件
        Sheet sheet = wb.getSheetAt(0);// 获取第一个工作表
        List<Bill> billList = new ArrayList<Bill>();

        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { // 从第二行开始读取
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                BigDecimal cash = new BigDecimal(ExcelUtil.getCellValue(row.getCell(0)));//读取现金
                String ptype = ExcelUtil.getCellValue(row.getCell(1));//收支类型
                String reason = ExcelUtil.getCellValue(row.getCell(2));//收支事由
                String payment = ExcelUtil.getCellValue(row.getCell(3));;//收支方式
                String name = ExcelUtil.getCellValue(row.getCell(4));;//姓名
                String relationship =ExcelUtil.getCellValue(row.getCell(5));;//关系
                Date pdate =Date.valueOf(ExcelUtil.getCellValue(row.getCell(6)) );//日期
                billList.add(new Bill(cash, ptype, reason, payment, name, relationship, pdate));
            }
        }

        //保存到数据库表
        for (int i=0; i<billList.size(); i++) {
            try {
                billService.createBill(billList.get(i));
            } catch (BizException e) {
                throw new RuntimeException(e);
            }
        }
        return Result.Success();
    }

   //按月获取当月账单
    @ResponseBody
    @RequestMapping("/listByMonth")
    public List<Bill> listByMonth(String yearMonth) {
        // 参数校验
        if (yearMonth == null || !yearMonth.matches("^\\d{4}-\\d{2}$")) {
            throw new IllegalArgumentException("参数格式应为6位数字(yyyy-MM)");
        }

        try {
            BillExample billExample = new BillExample();
            // 解析年月
            String year = yearMonth.substring(0, 4);
            String month = yearMonth.substring(4, 6);

           //转换为java.sql.Date
            LocalDate startDate = LocalDate.parse(yearMonth + "-01");
            LocalDate endDate = YearMonth.from(startDate).atEndOfMonth();

            // 添加日期范围条件
            billExample.createCriteria()
                    .andPdateGreaterThanOrEqualTo(Date.valueOf(startDate))
                    .andPdateLessThanOrEqualTo(Date.valueOf(endDate));

            return billService.findByExample(billExample);
        } catch (DateTimeException e) {
            throw new IllegalArgumentException("无效的日期参数", e);
        }
    }

}
